<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCimoTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //个人简历
        Schema::create('user_resumes', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('user_id')->unsigned()->comment('用户ID');
            $table->string('name',20)->nullable()->comment('真实姓名');
            $table->tinyInteger('completes')->default(0)->comment('完整度');
            $table->tinyInteger('highest_degree')->default(0)->comment('最高学历， 0：大专；1本科；2:硕士；3：博士；4其它； 默认0');
            $table->tinyInteger('gznx')->default(0)->comment('工作年限 0 应届毕业生 1：1年 ； 2：2年 。。。。10：年  ； >10 为10年以上');
            $table->string('phone',11)->comment('手机号');
            $table->string('email',30)->comment('邮箱');
            $table->integer('province')->default(0)->comment('省');
            $table->integer('city')->default(0)->comment('市');
            $table->integer('area')->default(0)->comment('区');
            $table->string('signature',255)->nullable()->comment('个性签名');
            $table->tinyInteger('is_closed')->default(0)->comment('简历状态，0:开启，1：关闭');
            $table->integer('status')->default(0)->comemnt('在职状态');
            $table->text('intro')->nullable()->comment('自我描述');
            $table->text('specialty')->nullable()->comment('特长');
            $table->tinyInteger('show_flag')->default(0)->comment('可见范围 0：公开 ，1：仅自己和投递企业可见 2：仅自己可见 ');
            $table->integer('views')->default(0)->comment('简历浏览量');

            $table->timestamps();
            $table->softDeletes();

            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
        });
        // 工作经历
        Schema::create('user_resume_experiences', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->string('company_name',200)->comment('公司名称');
            $table->string('job',100)->comment('职位');
            $table->integer('start_at')->comment('起始时间');
            $table->integer('end_at')->default(0)->comment('截至时间,0:至今');
            $table->text('content')->comment('工作内容');

            $table->timestamps();
            $table->foreign('resume_id')->references('id')->on('user_resumes')
                ->onUpdate('cascade')->onDelete('cascade');
        });
        //教育经历
        Schema::create('user_resume_educationals', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->string('school',200)->comment('学校名称');
            $table->string('major',200)->comment('专业');
            $table->tinyInteger('degree')->default(0)->comment('学历 0：大专；1本科；2:硕士；3：博士；4其它； 默认0');
            $table->integer('graduated_at')->comment('毕业年份');
            $table->tinyInteger('type_985')->default(0)->comment('是否为985院校  0 不是 ； 1是； 默认0 不是');
            $table->tinyInteger('type_211')->default(0)->comment('是否为 211院校 0 不是； 1 是； 默认 0 不是');

            $table->timestamps();
            $table->foreign('resume_id')->references('id')->on('user_resumes')
                ->onUpdate('cascade')->onDelete('cascade');
        });
        //项目经验
        Schema::create('user_resume_projects', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->string('name',200)->comment('项目名称');
            $table->string('duty',200)->comment('职责');
            $table->integer('start_at')->comment('项目起始时间');
            $table->integer('end_at')->default(0)->comment('截至时间,0:至今');
            $table->text('intro')->nullable()->comment('项目描述');
            $table->string('url',255)->default(0)->comment('项目链接');

            $table->timestamps();
            $table->foreign('resume_id')->references('id')->on('user_resumes')
                ->onUpdate('cascade')->onDelete('cascade');
        });
        // 我的作品
        Schema::create('user_resume_products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->string('name',200)->comment('作品名称');
            $table->text('intro')->nullable()->comment('作品描述');
            $table->string('imgae',255)->comment('作品图片地址');
            $table->string('link',255)->comment('链接');

            $table->timestamps();
            $table->foreign('resume_id')->references('id')->on('user_resumes')
            ->onUpdate('cascade')->onDelete('cascade');
        });

        // 简历标签
        Schema::create('user_resume_tag', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->integer('tag_id')->comment('标签ID');

            $table->timestamps();

            $table->foreign('resume_id')->references('id')->on('user_resumes')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('tag_id')->references('tags_id')->on('tags')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->primary(['resume_id','tag_id']);
        });

        Schema::create('user_needs', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('resume_id')->unsigned()->comment('简历ID');
            $table->integer('user_id')->unsigned()->comment('用户ID');
            $table->string('name')->comment('岗位名称');
            $table->integer('industry')->comment('行业');
            $table->integer('provice')->comment('省');
            $table->integer('city')->comment('市');
            $table->integer('area')->comment('区');
            $table->tinyInteger('expect_wages')->default(0)->comment('薪资 0:2k以下；1：2k-5K；2：5k-10k; 3:10k-15k; 4:15k-25k; 5:25k-50k; 6:50k以上 默认0');
            $table->tinyInteger('job_type')->default(0)->comment('职位类型  0 全职 ； 1  兼职 ； 2 实习。 默认0 全职');
            $table->integer('character_exam')->default(0)->comment('性格测试');
            $table->integer('skill_exam')->default(0)->comment('技能测试');

            $table->timestamps();

            $table->foreign('resume_id')->references('id')->on('user_resumes')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
        });
        //题库
        Schema::create('itembank', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('company_id')->default(0)->comment('企业编号,0:为系统题库');
            $table->string('name',200)->comment('题库名称');
            $table->text('intro')->nullable()->comment('题库描述');
            $table->integer('questions')->default(0)->comment('题目数量');

            $table->timestamps();
            $table->index('company_id');
        });
        // 题库与试题关联
        Schema::create('itembank_question', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->integer('itembank_id')->unsigned()->comment('题库ID');
            $table->integer('question_id')->comment('题目ID');

            $table->timestamps();

            $table->foreign('itembank_id')->references('id')->on('itembank')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->foreign('question_id')->references('id')->on('questions')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->primary(['itembank_id','question_id']);
        });
        //问题与职位关联
        Schema::create('question_job', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->integer('question_id')->comment('问题ID');
            $table->integer('job_id')->comment('职位ID');

            $table->timestamps();

            $table->foreign('question_id')->references('id')->on('questions')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->foreign('job_id')->references('cat_id')->on('company_jobs_type')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->primary(['question_id','job_id']);
        });
        //技能
        Schema::create('skills',function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name',100)->comment('技能名称');
            $table->integer('parent_id')->unsigned()->comment('父级ID');
            $table->smallInteger('sort')->unsigned()->default(0)->comment('排序');
            $table->text('intro')->nullable()->comment('描述');
            $table->timestamps();
        });

        //问题与技能
        Schema::create('question_skill', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->integer('question_id')->comment('问题ID');
            $table->integer('skill_id')->unsigned()->comment('技能ID');

            $table->timestamps();

            $table->foreign('question_id')->references('id')->on('questions')
            ->onUpdate('cascade')->onDelete('cascade');

            $table->foreign('skill_id')->references('id')->on('skills')
            ->onUpdate('cascade')->onDelete('cascade');

            $table->primary(['question_id','skill_id']);
        });
        //试卷
        Schema::create('exams',function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('company_id')->comment('企业ID');
            $table->string('name',80)->comment('名称');
            $table->integer('score_limit')->default(0)->comment('及格线：0:不限制');
            $table->integer('times')->default(0)->comment('考试时间，单位：分钟');
            $table->tinyInteger('radio_score')->default(0)->comment('单选分值');
            $table->tinyInteger('mulit_score')->default(0)->comment('多选分值');
            $table->tinyInteger('judge_score')->default(0)->comment('判断');
            $table->tinyInteger('discuss_score')->default(0)->comment('论述题');
            $table->integer('question_count')->comment('题量');

            $table->timestamps();

            $table->foreign('company_id')->references('id')->on('company')
                ->onUpdate('cascade')->onDelete('cascade');
        });

        //试题
        Schema::create('exam_question', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->integer('exam_id')->unsigned()->comment('试卷ID');
            $table->integer('question_id')->comment('试题ID');

            $table->timestamps();

            $table->foreign('exam_id')->references('id')->on('exams')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('question_id')->references('id')->on('questions')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->primary(['exam_id','question_id']);
        });

        //考试历史
        Schema::create('exam_history',function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('user_id')->unsigned()->comment('用户ID');
            $table->integer('company_id')->comment('企业ID');
            $table->integer('company_personal_id')->comment('企业人才库ID');
            $table->string('name')->comment('试卷名称');
            $table->integer('exam_id')->comment('试卷ID');
            $table->json('exam')->comment('试卷');
            $table->json('questions')->comment('题目');
            $table->json('user_answers')->comment('用户解答');
            $table->json('answers')->comment('正确答案');
            $table->json('score_lists')->comment('得分');
            $table->integer('score')->default(0)->comment('总分');
            $table->integer('user_time')->comment('用时');

            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('company_id')->references('id')->on('company')
                ->onUpdate('cascade')->onDelete('cascade');
        });

        //企业人才库
        Schema::create('company_personals', function (Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('company_id')->comment('企业ID');
            $table->integer('user_id')->unsigned()->comment('用户ID');
            $table->integer('need_id')->unsigned()->comment('需求ID');
            $table->string('job_name')->comment('岗位名称');
            $table->integer('industry')->comment('行业');
            $table->integer('provice')->comment('省');
            $table->integer('city')->comment('市');
            $table->integer('area')->comment('区');
            $table->tinyInteger('expect_wages')->default(0)->comment('薪资 0:2k以下；1：2k-5K；2：5k-10k; 3:10k-15k; 4:15k-25k; 5:25k-50k; 6:50k以上 默认0');
            $table->tinyInteger('job_type')->default(0)->comment('职位类型  0 全职 ； 1  兼职 ； 2 实习。 默认0 全职');
            $table->integer('character_exam')->default(0)->comment('性格测试');
            $table->integer('skill_exam')->default(0)->comment('技能测试');
            $table->json('resume')->comment('json数据，用户需求快照');

            $table->timestamps();

            $table->foreign('company_id')->references('id')->on('company')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->unique(['company_id','need_id']);
        });

        //企业黑名单 爽约三次自动加入黑名单 一年期满后自动清除
        Schema::create('company_backlists', function (Blueprint $table){
            $table->engine = 'InnoDB';
            $table->integer('company_id')->comment('企业ID');
            $table->integer('user_id')->unsigned()->comment('用户ID');

            $table->timestamps();

            $table->foreign('company_id')->references('id')->on('company')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');

            $table->primary(['company_id','user_id']);
        });


    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('user_resumes');
        Schema::drop('user_resume_experiences');
        Schema::drop('user_resume_educationals');
        Schema::drop('user_resume_projects');
        Schema::drop('user_resume_products');
        Schema::drop('user_resume_tag');
        Schema::drop('user_needs');
        Schema::drop('itembank');
        Schema::drop('itembank_question');
        Schema::drop('question_job');
        Schema::drop('skills');
        Schema::drop('question_skill');
        Schema::drop('exams');
        Schema::drop('exam_question');
        Schema::drop('exam_history');
        Schema::drop('company_personals');
        Schema::dropIfExists('company_personal_deals');
        Schema::drop('company_backlists');
    }
}
